﻿Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Public Class frmview_depot
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1
    Dim count_day As Integer = 0
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader

    Dim diff1 As System.TimeSpan

    Dim date1 As Date
    Dim date2 As Date
    Dim subdate1() As String
    Dim subdate2() As String
    Dim valuedate As String
    Dim dateend As String
    Dim nickctn As String
    Dim iddepot As String
    Dim idcontainer As String
    Dim voyagelist As String
    Dim containerlist As String
    Dim sizelist As String
    Dim agenlist As String
    Dim pathExcel As String
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Dim inbtIndex As Integer
    Dim ctnname As String
    Dim idctnnew As String
    Dim iddepotdetail As String = 0
    Dim containerString As String
    Dim iddepotunit As String
    Dim ctnstring As String
    Dim hrtotal As Integer = 0
    Dim departotal As Integer = 0
    Dim sumtotal As Integer = 0




    Private Sub frmview_depot_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'ListView1.Items.Clear()
        'ListView3.Items.Clear()
        'ListView4.Items.Clear()

        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage join ctnmain on voyage.VOYAGEID = ctnmain.CTNVOYN join ctndepot on ctnmain.CTNMAINID = ctndepot.CTNID where  CTNSTATDEPOT ='1';"        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            DataGridViewX1.Rows.Clear()
            While (mySqlReader.Read())

         
                    If mySqlReader("CTNSTATDEPOT") = 1 Then

                    DataGridViewX1.Rows.Add({mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), False, False, False, mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID")})

                    End If
           
                   


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


      
    End Sub
    Private Sub ButtonItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem4.Click
        Dim cf As New frmview_depotclean
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()

    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click

        Dim commandText2 As String
        If DataGridViewX2.Rows.Count > 0 Then
            For i = 0 To DataGridViewX2.Rows.Count - 1
                If DataGridViewX2.Rows(i).Cells(7).Value = 2 Then
                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try

                        commandText2 = "UPDATE ctndepot SET CTNSTATDEPOT = '0' ,  CTNSTATCHECK ='1', CTNDAYRE = '0'  ,CTNDATEIN ='" & Date.Now.Date.ToString("dd-MM-yyyy") & "'  , CTNDEDATEE = '" & Date.Now.Date.ToString("dd-MM-yyyy") & "'WHERE CTNDEPOTID = '" & DataGridViewX2.Rows(i).Cells(5).Value & "';"
                        mySqlCommand.CommandText = commandText2

                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()


                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try


                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try

                        commandText2 = "UPDATE ctnmain SET CTNSTAT = '1' WHERE CTNMAINID ='" & DataGridViewX2.Rows(i).Cells(6).Value & "'; "
                        mySqlCommand.CommandText = commandText2

                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()


                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                End If
               
            Next
        End If
        If DataGridViewX3.Rows.Count > 0 Then
            For i = 0 To DataGridViewX3.Rows.Count - 1
                If DataGridViewX3.Rows(i).Cells(7).Value = 2 Then
                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try

                        commandText2 = "UPDATE ctndepot SET CTNSTATDEPOT = '2' ,  CTNSTATCHECK ='0', CTNDAYRE = '0'  ,CTNDATEIN ='" & Date.Now.Date.ToString("dd-MM-yyyy") & "' WHERE CTNDEPOTID = '" & DataGridViewX3.Rows(i).Cells(5).Value & "';"
                        mySqlCommand.CommandText = commandText2

                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()


                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                End If
               
            Next
        End If
        If DataGridViewX4.Rows.Count > 0 Then
            For i = 0 To DataGridViewX4.Rows.Count - 1
                If DataGridViewX4.Rows(i).Cells(8).Value = 2 Then
                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try

                        commandText2 = "UPDATE ctndepot SET CTNSTATDEPOT = '3' ,  CTNSTATCHECK ='0'  ,CTNDAYRE = '0'  ,CTNDATEIN ='" & Date.Now.Date.ToString("dd-MM-yyyy") & "' WHERE CTNDEPOTID = '" & DataGridViewX4.Rows(i).Cells(6).Value & "';"
                        mySqlCommand.CommandText = commandText2

                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()


                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                End If

             
            Next
        End If
        DataGridViewX4.Rows.Clear()
        DataGridViewX3.Rows.Clear()
        DataGridViewX2.Rows.Clear()
    End Sub
    Private Sub ButtonItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem5.Click

        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelReport))
                t.Start()

            Catch ex As Exception

            End Try
        End If


    End Sub
    Private Sub excelReport()
        Dim count_Row As Integer = 5
        Dim count_Row2 As Integer = 9

        pathExcel = FolderBrowserDialog1.SelectedPath

        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Rows("3:3").rowheight = 20


        With (excelsheets)
            .Range("A1:Q900").Font.Name = "Angsana New"

            .Range("A2:Q900").Font.Size = 14
            Dim CheckIndex As Integer
            Dim i As Integer
            Dim CheckData As Boolean
            CheckData = False
            'CheckIndex = ListView1.Items.Count
            Dim J As Integer
            For J = 7 To 10
                .Range("A4").Borders(J).Weight = 2 ' xlThin
                .Range("B4").Borders(J).Weight = 2 ' xlThin
                .Range("C4").Borders(J).Weight = 2 ' xlThin
                .Range("D4").Borders(J).Weight = 2 ' xlThin
                .Range("E4").Borders(J).Weight = 2 ' xlThin
                .Range("F4").Borders(J).Weight = 2 ' xlThin
                .Range("G4").Borders(J).Weight = 2 ' xlThin
                .Range("H4").Borders(J).Weight = 2 ' xlThin


            Next
            With .Range("A2:F2")
                .Merge()


                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "YKP PORT CONTAINER DAMAGE"
                .Font.Size = 16
                ''.ColumnWidth = 20
            End With
            With .Range("A3:F3")
                .Merge()
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "จำนวนตู้รอตรวจสอบ"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("A4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Container No."
                .ColumnWidth = 15
            End With
            With .Range("B4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Agent Line"
                .ColumnWidth = 12
            End With
     
            With .Range("C4")


                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Date Recieve"
                .Font.Size = 14
                .ColumnWidth = 14
            End With
            With .Range("D4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Size"
                .Font.Size = 14
                ''.ColumnWidth = 12
            End With
            With .Range("E4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "VOYN"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("F4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ใช้งานได้"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("G4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ทำความสะอาด"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("H4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ซ่่อม"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select  * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where CTNSTAT= '0' and CTNVOYN ='" & TextBoxX1.Text & "' order by CTNMAINID ASC;"
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    With .Range("A" + count_Row.ToString)
                        .Value = mySqlReader("CTNSTRING")
                    End With

                    With .Range("B" + count_Row.ToString)
                        .Value = mySqlReader("CTNAGENT")
                    End With
                    With .Range("C" + count_Row.ToString)
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                        .Value = mySqlReader("VOYDATEEN")
                    End With
                    With .Range("D" + count_Row.ToString)
                        .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                        .Value = mySqlReader("CTNSIZE")
                    End With
                    With .Range("E" + count_Row.ToString)
                        .Value = Format(mySqlReader("VOYAGEID"), "000") + "N"
                    End With
                    count_Row += 1
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

         
            For i = 5 To count_Row
                .Range("B" & i.ToString() & ":B" & i.ToString()).Borders(7).Weight = 2
                .Range("A" & i.ToString() & ":A" & i.ToString()).Borders(7).Weight = 2
                .Range("C" & i.ToString() & ":C" & i.ToString()).Borders(7).Weight = 2
                .Range("D" & i.ToString() & ":D" & i.ToString()).Borders(7).Weight = 2
                .Range("E" & i.ToString() & ":E" & i.ToString()).Borders(7).Weight = 2
                .Range("F" & i.ToString() & ":F" & i.ToString()).Borders(7).Weight = 2
                .Range("G" & i.ToString() & ":G" & i.ToString()).Borders(7).Weight = 2
                .Range("H" & i.ToString() & ":H" & i.ToString()).Borders(7).Weight = 2
                .Range("I" & i.ToString() & ":I" & i.ToString()).Borders(7).Weight = 2
            Next
            count_Row += 1
            With .Range("A" + count_Row.ToString + ":H" + count_Row.ToString)
                .Borders(8).Weight = 2
            End With

        End With



        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportContainerDamage" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")
            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub
    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else


        End If
    End Sub
    Private Sub ButtonItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem1.Click
        Dim nextform As frmrpt_voyagedamage = New frmrpt_voyagedamage
        nextform.Show()
    End Sub
    Private Sub ButtonItem3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem3.Click

        Dim cf As New frmadd_ctndamage
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub ButtonItem7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem7.Click

        Dim cf As New frmrpt_depotdetail
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub DataGridViewX1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex

            DataGridViewX1.Rows(inbtIndex).Selected = True

        Catch ex As Exception

        End Try
        If e.ColumnIndex = 4 Then
            DataGridViewX1.Rows(inbtIndex).Cells(5).Value = False
            DataGridViewX1.Rows(inbtIndex).Cells(6).Value = False

        ElseIf e.ColumnIndex = 5 Then
            DataGridViewX1.Rows(inbtIndex).Cells(4).Value = False
            DataGridViewX1.Rows(inbtIndex).Cells(6).Value = False

        ElseIf e.ColumnIndex = 6 Then
            DataGridViewX1.Rows(inbtIndex).Cells(5).Value = False
            DataGridViewX1.Rows(inbtIndex).Cells(4).Value = False

        End If

    End Sub
    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click

        For counter1 = 0 To DataGridViewX1.Rows.Count - 1
            iddepot = DataGridViewX1.Rows(counter1).Cells(7).Value
            idcontainer = DataGridViewX1.Rows(counter1).Cells(8).Value
            voyagelist = DataGridViewX1.Rows(counter1).Cells(0).Value
            containerlist = DataGridViewX1.Rows(counter1).Cells(1).Value
            sizelist = DataGridViewX1.Rows(counter1).Cells(2).Value
            agenlist = DataGridViewX1.Rows(counter1).Cells(3).Value

            If DataGridViewX1.Rows(counter1).Cells(4).Value = True Then
                DataGridViewX2.Rows.Add({voyagelist, containerlist, sizelist, agenlist, "พร้อมใช้งาน", iddepot, idcontainer, "2"})


            ElseIf DataGridViewX1.Rows(counter1).Cells(5).Value = True Then
                DataGridViewX3.Rows.Add({voyagelist, containerlist, sizelist, agenlist, "ทำความสะอาด", iddepot, idcontainer, "2"})


            ElseIf DataGridViewX1.Rows(counter1).Cells(6).Value = True Then
                DataGridViewX4.Rows.Add({False, voyagelist, containerlist, sizelist, agenlist, "รอซ่อม", iddepot, idcontainer, "2"})


            End If



        Next
        For i As Integer = DataGridViewX1.Rows.Count - 1 To 0 Step -1
            If CBool(DataGridViewX1.Rows(i).Cells(4).FormattedValue) Then

                DataGridViewX1.Rows.RemoveAt(i)
            ElseIf CBool(DataGridViewX1.Rows(i).Cells(5).FormattedValue) Then
                DataGridViewX1.Rows.RemoveAt(i)
            ElseIf CBool(DataGridViewX1.Rows(i).Cells(6).FormattedValue) Then
                DataGridViewX1.Rows.RemoveAt(i)
            End If
        Next





        For i As Integer = 0 To DataGridViewX2.Rows.Count - 1

            If DataGridViewX2.Rows(i).Cells(7).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX2.Rows(i).Cells(7).Value = 0 Then


                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX2.Rows(i).Cells(7).Value = 1 Then
                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.DeepSkyBlue
            End If

        Next





        For i As Integer = 0 To DataGridViewX3.Rows.Count - 1

            If DataGridViewX3.Rows(i).Cells(7).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX3.Rows(i).Cells(7).Value = 0 Then


                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX3.Rows(i).Cells(7).Value = 1 Then
                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.DeepSkyBlue
            End If

        Next



        For i As Integer = 0 To DataGridViewX4.Rows.Count - 1

            If DataGridViewX4.Rows(i).Cells(8).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX4.Rows(i).Cells(8).Value = 0 Then

                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX4.Rows(i).Cells(8).Value = 1 Then
                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.DeepSkyBlue
            End If

        Next
    End Sub
    Private Sub DataGridViewX2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX2.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex

            DataGridViewX2.Rows(inbtIndex).Selected = True

            iddepot = DataGridViewX2.Rows(inbtIndex).Cells(5).Value

            idctnnew = DataGridViewX2.Rows(inbtIndex).Cells(6).Value
        Catch ex As Exception

        End Try
    End Sub
    Private Sub DataGridViewX2_CellMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridViewX2.CellMouseDoubleClick
        Try
            If DataGridViewX2.SelectedRows.Count > 0 Then

                If DataGridViewX2.CurrentCell.Value Is Nothing Then

                Else
                    If DataGridViewX2.Rows(inbtIndex).Cells(7).Value = 2 Then
                        DataGridViewX1.Rows.Add({DataGridViewX2.Rows(inbtIndex).Cells(0).Value, DataGridViewX2.Rows(inbtIndex).Cells(1).Value, DataGridViewX2.Rows(inbtIndex).Cells(2).Value, DataGridViewX2.Rows(inbtIndex).Cells(3).Value, False, False, False, DataGridViewX2.Rows(inbtIndex).Cells(5).Value, DataGridViewX2.Rows(inbtIndex).Cells(6).Value})
                        DataGridViewX2.Rows.RemoveAt(inbtIndex)

                    End If
                End If


            End If

            iddepot = DataGridViewX2.Rows(inbtIndex).Cells(5).Value

            idctnnew = DataGridViewX2.Rows(inbtIndex).Cells(6).Value

        Catch ex As Exception

        End Try



    End Sub
    Private Sub DataGridViewX3_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX3.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex

            DataGridViewX3.Rows(inbtIndex).Selected = True
            iddepot = DataGridViewX3.Rows(inbtIndex).Cells(5).Value

            idctnnew = DataGridViewX3.Rows(inbtIndex).Cells(6).Value
        Catch ex As Exception

        End Try
    End Sub
    Private Sub DataGridViewX3_CellMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridViewX3.CellMouseDoubleClick
        Try
            If DataGridViewX3.SelectedRows.Count > 0 Then
                If DataGridViewX3.CurrentCell.Value Is Nothing Then
                Else

                    If DataGridViewX3.Rows(inbtIndex).Cells(7).Value = 2 Then
                        DataGridViewX1.Rows.Add({DataGridViewX3.Rows(inbtIndex).Cells(0).Value, DataGridViewX3.Rows(inbtIndex).Cells(1).Value, DataGridViewX3.Rows(inbtIndex).Cells(2).Value, DataGridViewX3.Rows(inbtIndex).Cells(3).Value, False, False, False, DataGridViewX3.Rows(inbtIndex).Cells(5).Value, DataGridViewX3.Rows(inbtIndex).Cells(6).Value})
                        DataGridViewX3.Rows.RemoveAt(inbtIndex)
                    End If
                End If
            End If
            iddepot = DataGridViewX3.Rows(inbtIndex).Cells(5).Value

            idctnnew = DataGridViewX3.Rows(inbtIndex).Cells(6).Value
        Catch ex As Exception

        End Try

    End Sub
    Private Sub DataGridViewX4_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX4.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex
            iddepot = DataGridViewX4.Rows(inbtIndex).Cells(6).Value
            DataGridViewX4.Rows(inbtIndex).Selected = True

            idctnnew = DataGridViewX4.Rows(inbtIndex).Cells(7).Value
        Catch ex As Exception

        End Try
    End Sub
    Private Sub TextBoxX1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX1.KeyDown

        If e.KeyCode = Keys.Enter Then
            DataGridViewX1.Rows.Clear()
            DataGridViewX2.Rows.Clear()
            DataGridViewX3.Rows.Clear()
            DataGridViewX4.Rows.Clear()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from voyage join ctnmain on voyage.VOYAGEID = ctnmain.CTNVOYN join ctndepot on ctnmain.CTNMAINID = ctndepot.CTNID where  VOYAGEID= '" & TextBoxX1.Text & "';"        ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                DataGridViewX1.Rows.Clear()
                While (mySqlReader.Read())
                    If mySqlReader("CTNDEDATEE") Is DBNull.Value Or mySqlReader("CTNDEDATEE").ToString.Length < 1 Then
                        valuedate = ""
                        dateend = ""
                    Else
                        subdate1 = Split(mySqlReader("CTNDEDATEE"), "-")
                        subdate2 = Split(mySqlReader("CTNDEDATES"), "-")
                        date1 = New System.DateTime(subdate1(2), subdate1(1), subdate1(0), 0, 0, 0)
                        date2 = New System.DateTime(subdate2(2), subdate2(1), subdate2(0), 0, 0, 0)
                        diff1 = date1.Subtract(date2)
                        valuedate = diff1.TotalDays.ToString
                        dateend = mySqlReader("CTNDEDATEE")
                    End If

                    If mySqlReader("CTNSTATDEPOT") = 1 Then

                        DataGridViewX1.Rows.Add({mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), False, False, False, mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID")})
                    ElseIf mySqlReader("CTNSTATDEPOT") = 0 Then
                        DataGridViewX2.Rows.Add({mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), "พร้อมใช้งาน", mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID"), mySqlReader("CTNSTATCHECK"), dateend, valuedate})

                    ElseIf mySqlReader("CTNSTATDEPOT") = 2 Then
                        If mySqlReader("CTNSTATCHECK") = 1 Then
                            DataGridViewX3.Rows.Add({mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), "พร้อมใช้งาน", mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID"), mySqlReader("CTNSTATCHECK"), dateend, valuedate})
                        Else
                            DataGridViewX3.Rows.Add({mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), "ทำความสะอาด", mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID"), mySqlReader("CTNSTATCHECK"), dateend, valuedate})

                        End If
                    ElseIf mySqlReader("CTNSTATDEPOT") = 3 Then
                        If mySqlReader("CTNSTATCHECK") = 1 Then
                            DataGridViewX4.Rows.Add({False, mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), "พร้อมใช้งาน", mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID"), mySqlReader("CTNSTATCHECK"), dateend, valuedate})
                        Else
                            DataGridViewX4.Rows.Add({False, mySqlReader("VOYAGEID"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), "รอซ่อม", mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID"), mySqlReader("CTNSTATCHECK"), dateend, valuedate})

                        End If

                    End If




                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()





        End If

        For i As Integer = 0 To DataGridViewX2.Rows.Count - 1

            If DataGridViewX2.Rows(i).Cells(7).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX2.Rows(i).Cells(7).Value = 0 Then


                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX2.Rows(i).Cells(7).Value = 1 Then
                DataGridViewX2.Rows(i).Cells(4).Style.BackColor = Color.DeepSkyBlue
            End If

        Next





        For i As Integer = 0 To DataGridViewX3.Rows.Count - 1

            If DataGridViewX3.Rows(i).Cells(7).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX3.Rows(i).Cells(7).Value = 0 Then


                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX3.Rows(i).Cells(7).Value = 1 Then
                DataGridViewX3.Rows(i).Cells(4).Style.BackColor = Color.DeepSkyBlue
            End If

        Next



        For i As Integer = 0 To DataGridViewX4.Rows.Count - 1

            If DataGridViewX4.Rows(i).Cells(8).Value = 2 Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.Yellow
            ElseIf DataGridViewX4.Rows(i).Cells(8).Value = 0 Then

                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX4.Rows(i).Cells(8).Value = 1 Then
                DataGridViewX4.Rows(i).Cells(6).Style.BackColor = Color.DeepSkyBlue
            End If

        Next

    End Sub
    Private Sub DataGridViewX4_CellMouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridViewX4.CellMouseDoubleClick
        Try
            If DataGridViewX4.SelectedRows.Count > 0 Then
                If DataGridViewX4.CurrentCell.Value Is Nothing Then

                Else
                    If DataGridViewX4.Rows(inbtIndex).Cells(8).Value = 2 Then
                        DataGridViewX1.Rows.Add({DataGridViewX4.Rows(inbtIndex).Cells(1).Value, DataGridViewX4.Rows(inbtIndex).Cells(2).Value, DataGridViewX4.Rows(inbtIndex).Cells(3).Value, DataGridViewX4.Rows(inbtIndex).Cells(4).Value, False, False, False, DataGridViewX4.Rows(inbtIndex).Cells(6).Value, DataGridViewX4.Rows(inbtIndex).Cells(7).Value})
                        DataGridViewX4.Rows.RemoveAt(inbtIndex)
                    End If

                End If

                iddepot = DataGridViewX4.Rows(inbtIndex).Cells(6).Value

                idctnnew = DataGridViewX4.Rows(inbtIndex).Cells(7).Value
            End If
        Catch ex As Exception

        End Try

    End Sub
    Private Sub ButtonItem8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem8.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelreportRepair))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try

        End If

    End Sub
    Private Sub excelMalay()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets


            Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_wanhai.png", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            '.DisplayGridLines = False
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10
            .Range("C1").ColumnWidth = 4.63
            .Range("D1").ColumnWidth = 8.52
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 15.13
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            .Range("I1").ColumnWidth = 11.13
            .Range("J1").ColumnWidth = 14.63
            For J = 7 To 10
                .Range("A6:B6").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C6").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D6").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E6").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F6").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H6").Borders(J).Weight = 3 ' xlThin
                .Range("I6:J6").Borders(J).Weight = 3 ' xlThin
                .Range("A7:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C7:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D7:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E7:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F7:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G7:H7").Borders(J).Weight = 3 ' xlThin
                .Range("I7:J7").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:G10").Borders(J).Weight = 3 ' xlThin
                .Range("H10:H10").Borders(J).Weight = 3 ' xlThin
                .Range("I10:I10").Borders(J).Weight = 3 ' xlThin
                .Range("J10:J10").Borders(J).Weight = 3 ' xlThin
                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next

            For J = 11 To 29
                .Range("A" + J.ToString + ":J" + J.ToString).Borders(9).Weight = 2 ' xlThin
            Next
            .Range("A11:A30").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B30").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F30").Borders(7).Weight = 3 ' xlThin
            .Range("G11:G30").Borders(7).Weight = 3 ' xlThin
            .Range("H11:H31").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(10).Weight = 3 ' xlThin
            .Range("H42:J42").Borders(9).Weight = 2 ' xlThin
            .Range("A44:J44").Borders(9).Weight = 2 ' xlThin
            .Range("A51:J51").Borders(9).Weight = 2 ' xlThin

            .Range("A1:L500").Font.Name = "Times New Roman"
            .Range("A1:L500").Font.Size = 8
            .Rows("1:1").rowheight = 30
            .Rows("2:2").rowheight = 20.25
            .Rows("3:3").rowheight = 20.25

            .Rows("4:4").rowheight = 20.25
            .Rows("5:31").rowheight = 16.25

            .Rows("8:8").rowheight = 15.75
            .Rows("10:10").rowheight = 17.25
            .Rows("33:54").rowheight = 15.75
            With .Range("A1:J1")
                .Merge()
                .Font.Size = 24
                .Value = "WAN HAI LINES LTD."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Font.Size = 10
                .Value = "YUSOB PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:J2")
                .Merge()
                .Font.Size = 16
                .Value = "EQUIPMENT REPAIR ESTIMATION REPORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I4:I4")
                .Font.Size = 12
                .Value = "EST NO.KAN 03"
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A6:B6")
                .Merge()
                .Font.Size = 10
                .Value = "CONTAINER NO"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Font.Size = 10
                .Value = ctnstring
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C6:C6")
                .Merge()
                .Font.Size = 10
                .Value = "SIZE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D6:D6")
                .Merge()
                .Font.Size = 10
                .Value = "EST.DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E6:E6")
                .Merge()
                .Font.Size = 10
                .Value = "LOADING PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F6:F6")
                .Merge()
                .Font.Size = 10
                .Value = "IMPORT DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G6:H6")
                .Merge()
                .Font.Size = 10
                .Value = "IMP VSL & VOY"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I6:J6")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A10:A10")

                .Font.Size = 10
                .Value = "ITEM"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Italic = True
                .Font.Bold = True

            End With
            With .Range("B10:E10")
                .Merge()
                .Font.Size = 10
                .Value = "DESCRIPTION OF DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F10:F10")

                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G10:G10")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("H10:H10")
                .Merge()
                .Font.Size = 10
                .Value = "MAN HOUR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("I10:I10")
                .Merge()
                .Font.Size = 10
                .Value = "LABOR COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J10:J10")
                .Merge()
                .Font.Size = 10
                .Value = "MATERIAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("A31:G31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A33:D33")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True

            End With

            With .Range("A38:C38")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A40:C40")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A42:C42")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("H42:I42")
                .Merge()
                .Font.Size = 10
                .Value = "APPROVED BY :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A44:C44")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A31:G31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Italic = True
            End With
            With .Range("A45:D45")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ----------------------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A46:D46")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN------------- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A47:D47")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP ------------------- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A48:D48")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ------------- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A49:D49")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH --------------------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E45:F45")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION ---------------------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E46:F46")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE --------------------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E47:F47")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT ------------------------ INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E48:F48")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE ----- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E49:F49")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------------------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("G45:I45")
                .Merge()
                .Font.Size = 10
                .Value = "FREE --------------------------- FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A51:B51")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A53:J53")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A54:J54")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A52:J52")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            Dim countline As Integer = 12
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0
            Dim dmtotal As Integer = 0
            Dim wttotal As Integer = 0
            Dim cntotal As Integer = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot left join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A" + countline.ToString + ":A" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = countitem.ToString
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countitem += 1

                    With .Range("B" + countline.ToString + ":E" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCOMMENTENG")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                        .Font.Bold = True

                    End With
                    With .Range("F" + countline.ToString + ":F" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODEREPAIR")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    With .Range("G" + countline.ToString + ":G" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("H" + countline.ToString + ":H" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNDEPOTHR")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("I" + countline.ToString + ":I" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICE")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("J" + countline.ToString + ":J" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICEDEPART")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countline += 1

                    man_hour += CDbl(mySqlReader("CTNDEPOTHR"))

                    hrtotal += CInt(mySqlReader("CTNPRICE"))
                    departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                    sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))

                    If mySqlReader("CTNCODE") = "DM" Then
                        dmtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "WT" Then
                        wttotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "CN" Then
                        cntotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    End If

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()




            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    With .Range("A7:B7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSTRING")
                        containerlist = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    Dim a As String
                    a = Mid(containerlist, 1, 1)
                    With .Range("C7:C7")
                        .Merge()
                        .Font.Size = 10
                        If a = "W" Then
                            .Value = "W496"
                        Else
                            .Value = "S496"
                        End If

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("F7:F7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("G7:H7")
                        .Merge()
                        .Font.Size = 10
                        .WrapText = True
                        .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With



                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try



            With .Range("H31:H31")
                .Merge()
                .Font.Size = 10
                .Value = man_hour
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("I31:I31")
                .Merge()
                .Font.Size = 10
                .Value = hrtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = departotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D38:D38")
                .Merge()
                .Font.Size = 10
                If dmtotal > 0 Then
                    .Value = dmtotal
                Else

                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D40:D40")
                .Merge()
                .Font.Size = 10
                If wttotal > 0 Then
                    .Value = wttotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D42:D42")
                .Merge()
                .Font.Size = 10
                If cntotal > 0 Then
                    .Value = cntotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("I7:J7")
                .Merge()
                .Font.Size = 10
                .Value = sumtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

        End With
        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "" + containerlist + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
            CircularProgress1.IsRunning = False
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")
            CircularProgress1.IsRunning = False
        End Try

    End Sub
    Private Sub excelOther()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        Dim excelsheets2 As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets2 = CType(excelbooks.Worksheets(2), Excel.Worksheet)

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape

            .Range("A1").ColumnWidth = 6.0
            .Range("B1").ColumnWidth = 11.25
            .Range("C1").ColumnWidth = 9.13
            .Range("D1").ColumnWidth = 12.63
            .Range("E1").ColumnWidth = 12.88
            .Range("F1").ColumnWidth = 12.63
            .Range("G1").ColumnWidth = 4.88
            .Range("H1").ColumnWidth = 14.13
            .Range("I1").ColumnWidth = 10.75
            .Range("J1").ColumnWidth = 17.88


            .Range("A1:L200").Font.Name = "Angsana New"
            .Range("A1:L200").Font.Size = 16

            .Rows("1:1").rowheight = 32.25
            .Rows("2:2").rowheight = 28.5
            .Rows("3:4").rowheight = 20.25
            .Rows("5:5").rowheight = 32.25
            .Rows("6:6").rowheight = 21.75
            .Rows("7:8").rowheight = 18
            .Rows("9:9").rowheight = 20.25
            .Rows("10:10").rowheight = 28.5
            .Rows("11:11").rowheight = 18.75
            .Rows("12:41").rowheight = 17


            For J = 7 To 10
                .Range("A6:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H7").Borders(J).Weight = 3 ' xlThin

                .Range("A8:B8").Borders(J).Weight = 3 ' xlThin
                .Range("C8:C8").Borders(J).Weight = 3 ' xlThin
                .Range("D8:D8").Borders(J).Weight = 3 ' xlThin
                .Range("E8:E8").Borders(J).Weight = 3 ' xlThin
                .Range("F8:F8").Borders(J).Weight = 3 ' xlThin
                .Range("G8:H8").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:H10").Borders(J).Weight = 3 ' xlThin

                .Range("A11:A11").Borders(J).Weight = 3 ' xlThin
                .Range("B11:E11").Borders(J).Weight = 3 ' xlThin
                .Range("F11:F11").Borders(J).Weight = 3 ' xlThin
                .Range("G11:H11").Borders(J).Weight = 3 ' xlThin

                '----
                .Range("A27:E27").Borders(J).Weight = 3 ' xlThin
                .Range("F27:F27").Borders(J).Weight = 3 ' xlThin

                .Range("G27:H27").Borders(J).Weight = 3 ' xlThin
            Next


            .Range("A11:A27").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B27").Borders(7).Weight = 3 ' xlThin

            .Range("F11:F27").Borders(7).Weight = 3 ' xlThin

            .Range("G11:G27").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I27").Borders(7).Weight = 3 ' xlThin

            .Range("A32:H32").Borders(9).Weight = 2 ' xlThin
            .Range("A38:H38").Borders(9).Weight = 2 ' xlThin
            .Range("A41:H41").Borders(9).Weight = 2 ' xlThin


            With .Range("A1:H1")
                .Merge()
                .Font.Size = 36
                .Value = "บริษัท วายเคพี ดีโป จำกัด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:H2")
                .Merge()
                .Font.Size = 22
                .Value = "YKP DEPOT CO.,LTD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A3:H3")
                .Merge()
                .Font.Size = 22
                .Value = "87/1 หมู่ 5 ตำบลบ่อน้ำร้อน อำเภอกันตัง จังหวัดตรัง 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With
            With .Range("A4:H4")
                .Merge()
                .Font.Size = 22
                .Value = "87/1 MOO 5 BONAMRON, KANTANG, TRANG 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With


            With .Range("A6:B6")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C6:C6")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C7:C7")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D6:D6")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D7:D7")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E6:E6")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F6:F6")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G6:H6")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A10:A10")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A11:A11")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B10:E10")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("B11:E11")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With



            With .Range("F10:F10")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("F11:F11")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With




            With .Range("G10:H10")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("G11:H11")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With










            With .Range("A28:C28")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True

            End With

            With .Range("A29:D29")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST เสียหาย :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A30:D30")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST เสื่อมสภาพ :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A31:D31")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST ทำความสะอาด :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F31:H31")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :.................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A32:C32")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD โค๊ซที่ใช้ในการเขียนเพื่อซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A33:D33")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ------- เชื่อม-------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A34:D34")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN-----ดัด----- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A35:D35")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP --ตั้งศูนย์ประตูใหม่-- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A36:D36")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ---ดัดและเชื่อม--- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A37:D37")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH -------ตัดปะ-------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E33:E33")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION -------ตัดต่อ--------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E34:E34")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE ------เปลี่ยน----------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E35:E35")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT -------ตัดและสอด--------- INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E36:E36")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE --ถอดประกอบ-- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E37:E37")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------เอาออก-------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("F33:H33")
                .Merge()
                .Font.Size = 10
                .Value = "FREE -----ทำให้คล่อง------ FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A38:B38")
                .Merge()
                .Font.Size = 10
                .Value = "CODE โค๊ซบอกอาการ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A39:E39")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE   เสียหาย"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A40:E40")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR   เสื่อมสภาพ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A41:J41")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING   ทำความสะอาด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With


            Dim countline As Integer = 12
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0
            Dim dmtotal As Integer = 0
            Dim wttotal As Integer = 0
            Dim cntotal As Integer = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot left join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A" + countline.ToString + ":A" + countline.ToString)
                        .Merge()
                        .Font.Size = 16
                        .Value = countitem.ToString
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countitem += 1

                    With .Range("B" + countline.ToString + ":E" + countline.ToString)
                        .Merge()
                        .Font.Size = 16
                        .Value = mySqlReader("CTNCOMMENTENG")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                        .Font.Bold = True

                    End With
                    With .Range("F" + countline.ToString + ":F" + countline.ToString)
                        .Merge()
                        .Font.Size = 16
                        .Value = mySqlReader("CTNCODEREPAIR")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    With .Range("G" + countline.ToString + ":H" + countline.ToString)
                        .Merge()
                        .Font.Size = 16
                        .Value = mySqlReader("CTNCODE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
      
      
               

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()




            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    With .Range("A8:B8")
                        .Merge()
                        .Font.Size = 16
                        .Value = mySqlReader("CTNSTRING")
                        containerlist = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    Dim a As String
                    a = Mid(containerlist, 1, 1)
                    With .Range("C8:C8")
                        .Merge()
                        .Font.Size = 16
                        If a = "W" Then
                            .Value = "W496"
                        Else
                            .Value = "S496"
                        End If

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

       
                      


                    With .Range("F8:F8")
                        .Merge()
                        .Font.Size = 16
                        .Value = mySqlReader("VOYDATEEN")

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("G8:H8")
                        .Merge()
                        .Font.Size = 16

                        .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With



                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try


        End With


            With excelsheets2


                Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_logo.jpg", False, True, 1, 1, 70, 70)
                Catch ex As Exception

                End Try
                .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
                .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
                '.DisplayGridLines = False
                .Range("A1").ColumnWidth = 4.75
                .Range("B1").ColumnWidth = 10
                .Range("C1").ColumnWidth = 4.63
                .Range("D1").ColumnWidth = 8.52
                .Range("E1").ColumnWidth = 13.25
                .Range("F1").ColumnWidth = 15.13
                .Range("G1").ColumnWidth = 5.25
                .Range("H1").ColumnWidth = 10.75
                .Range("I1").ColumnWidth = 11.13
                .Range("J1").ColumnWidth = 14.63
                For J = 7 To 10
                    .Range("A6:B6").Borders(J).Weight = 3 ' xlThin
                    .Range("C6:C6").Borders(J).Weight = 3 ' xlThin
                    .Range("D6:D6").Borders(J).Weight = 3 ' xlThin
                    .Range("E6:E6").Borders(J).Weight = 3 ' xlThin
                    .Range("F6:F6").Borders(J).Weight = 3 ' xlThin
                    .Range("G6:H6").Borders(J).Weight = 3 ' xlThin
                    .Range("I6:J6").Borders(J).Weight = 3 ' xlThin
                    .Range("A7:B7").Borders(J).Weight = 3 ' xlThin
                    .Range("C7:C7").Borders(J).Weight = 3 ' xlThin
                    .Range("D7:D7").Borders(J).Weight = 3 ' xlThin
                    .Range("E7:E7").Borders(J).Weight = 3 ' xlThin
                    .Range("F7:F7").Borders(J).Weight = 3 ' xlThin
                    .Range("G7:H7").Borders(J).Weight = 3 ' xlThin
                    .Range("I7:J7").Borders(J).Weight = 3 ' xlThin

                    '-----
                    .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                    .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                    .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                    .Range("G10:G10").Borders(J).Weight = 3 ' xlThin
                    .Range("H10:H10").Borders(J).Weight = 3 ' xlThin
                    .Range("I10:I10").Borders(J).Weight = 3 ' xlThin
                    .Range("J10:J10").Borders(J).Weight = 3 ' xlThin
                    '----
                    .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                    .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                    .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                    .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
                Next

                For J = 11 To 29
                    .Range("A" + J.ToString + ":J" + J.ToString).Borders(9).Weight = 2 ' xlThin
                Next
                .Range("A11:A30").Borders(7).Weight = 3 ' xlThin
                .Range("B11:B30").Borders(7).Weight = 3 ' xlThin
                .Range("F11:F30").Borders(7).Weight = 3 ' xlThin
                .Range("G11:G30").Borders(7).Weight = 3 ' xlThin
                .Range("H11:H31").Borders(7).Weight = 3 ' xlThin
                .Range("I11:I31").Borders(7).Weight = 3 ' xlThin
                .Range("J11:J31").Borders(7).Weight = 3 ' xlThin
                .Range("J11:J31").Borders(10).Weight = 3 ' xlThin
                .Range("H42:J42").Borders(9).Weight = 2 ' xlThin
                .Range("A44:J44").Borders(9).Weight = 2 ' xlThin
                .Range("A51:J51").Borders(9).Weight = 2 ' xlThin

                .Range("A1:L500").Font.Name = "Times New Roman"
                .Range("A1:L500").Font.Size = 8
                .Rows("1:1").rowheight = 30
                .Rows("2:2").rowheight = 20.25
                .Rows("3:3").rowheight = 20.25

                .Rows("4:4").rowheight = 20.25
                .Rows("5:31").rowheight = 16.25

                .Rows("8:8").rowheight = 15.75
                .Rows("10:10").rowheight = 17.25
            .Rows("33:54").rowheight = 15.75

         
            With .Range("A2:J2")
                .Merge()
                .Font.Size = 18
                .Value = "YKP DEPOT CO.,LTD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A3:J3")
                .Merge()
                .Font.Size = 18
                .Value = "87/1 หมู่ 5 ตำบลบ่อน้ำร้อน อำเภอกันตัง จังหวัดตรัง 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With
            With .Range("A4:J4")
                .Merge()
                .Font.Size = 18
                .Value = "87/1 MOO 5 BONAMRON, KANTANG, TRANG 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With




                With .Range("A1:J1")
                    .Merge()
                .Font.Size = 18
                .Value = "บริษัท วายเคพี ดีโป จำกัด"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("E7:E7")
                    .Merge()
                    .Font.Size = 10
                    .Value = "YUSOB PORT"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
     
       

                With .Range("A6:B6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "CONTAINER NO"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("A7:B7")
                    .Merge()
                    .Font.Size = 10
                    .Value = ctnstring
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("C6:C6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "SIZE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("D6:D6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "EST.DATE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("E6:E6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "LOADING PORT"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("F6:F6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "IMPORT DATE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("G6:H6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "IMP VSL & VOY"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("I6:J6")
                    .Merge()
                    .Font.Size = 10
                    .Value = "TOTAL COST"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("A10:A10")

                    .Font.Size = 10
                    .Value = "ITEM"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Italic = True
                    .Font.Bold = True

                End With
                With .Range("B10:E10")
                    .Merge()
                    .Font.Size = 10
                    .Value = "DESCRIPTION OF DAMAGE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("F10:F10")

                    .Font.Size = 10
                    .Value = "REPAIR METHOD"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("G10:G10")
                    .Merge()
                    .Font.Size = 10
                    .Value = "CODE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("H10:H10")
                    .Merge()
                    .Font.Size = 10
                    .Value = "MAN HOUR"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("I10:I10")
                    .Merge()
                    .Font.Size = 10
                    .Value = "LABOR COST"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("J10:J10")
                    .Merge()
                    .Font.Size = 10
                    .Value = "MATERIAL COST"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                    .Font.Italic = True
                End With
                With .Range("A31:G31")
                    .Merge()
                    .Font.Size = 10
                    .Value = "TOTAL"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("A33:D33")
                    .Merge()
                    .Font.Size = 10
                    .Value = "LOCAL CURRENCY : MYR"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True

                End With

                With .Range("A38:C38")
                    .Merge()
                    .Font.Size = 10
                    .Value = "DAMAGED COST :"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True
                End With
                With .Range("A40:C40")
                    .Merge()
                    .Font.Size = 10
                    .Value = "WEAR & TEAR COST :"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True
                End With
                With .Range("A42:C42")
                    .Merge()
                    .Font.Size = 10
                    .Value = "CLEANING COST :"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True
                End With
                With .Range("H42:I42")
                    .Merge()
                    .Font.Size = 10
                    .Value = "APPROVED BY :"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True
                End With

                With .Range("A44:C44")
                    .Merge()
                    .Font.Size = 10
                    .Value = "REPAIR METHOD"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A31:G31")
                    .Merge()
                    .Font.Size = 10
                    .Value = "TOTAL"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Italic = True
                End With
                With .Range("A45:D45")
                    .Merge()
                    .Font.Size = 10
                    .Value = "WELD ----------------------- WLD"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A46:D46")
                    .Merge()
                    .Font.Size = 10
                    .Value = "STRAIGHTEN------------- STR"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A47:D47")
                    .Merge()
                    .Font.Size = 10
                    .Value = "JACK UP ------------------- JAK"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A48:D48")
                    .Merge()
                    .Font.Size = 10
                    .Value = "STR & WELD ------------- S&W"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A49:D49")
                    .Merge()
                    .Font.Size = 10
                    .Value = "PATCH --------------------- PTH"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With





                With .Range("E45:F45")
                    .Merge()
                    .Font.Size = 10
                    .Value = "SECTION ---------------------- SEC"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("E46:F46")
                    .Merge()
                    .Font.Size = 10
                    .Value = "REPLACE --------------------- RPL"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("E47:F47")
                    .Merge()
                    .Font.Size = 10
                    .Value = "INSERT ------------------------ INS"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("E48:F48")
                    .Merge()
                    .Font.Size = 10
                    .Value = "REFIT OR RESECURE ----- RFT"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("E49:F49")
                    .Merge()
                    .Font.Size = 10
                    .Value = "REMOVE ---------------------- RMV"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With

                With .Range("G45:I45")
                    .Merge()
                    .Font.Size = 10
                    .Value = "FREE --------------------------- FRE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A51:B51")
                    .Merge()
                    .Font.Size = 10
                    .Value = "CODE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A53:J53")
                    .Merge()
                    .Font.Size = 10
                    .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A54:J54")
                    .Merge()
                    .Font.Size = 10
                    .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                With .Range("A52:J52")
                    .Merge()
                    .Font.Size = 10
                    .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                End With
                Dim countline As Integer = 12
                Dim countitem As Integer = 1
                Dim man_hour As Double = 0
                Dim dmtotal As Integer = 0
                Dim wttotal As Integer = 0
                Dim cntotal As Integer = 0

                hrtotal = 0

                departotal = 0
                sumtotal = 0
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If

                mySqlCommand.CommandText = "Select * from ctndepot left join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
                ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

                mySqlCommand.Connection = mysql
                mySqlAdaptor.SelectCommand = mySqlCommand
                Try
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())


                        With .Range("A" + countline.ToString + ":A" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = countitem.ToString
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True

                        End With
                        countitem += 1

                        With .Range("B" + countline.ToString + ":E" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNCOMMENTENG")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                            .Font.Bold = True

                        End With
                        With .Range("F" + countline.ToString + ":F" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNCODEREPAIR")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True
                        End With
                        With .Range("G" + countline.ToString + ":G" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNCODE")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True

                        End With
                        With .Range("H" + countline.ToString + ":H" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNDEPOTHR")
                            .NumberFormat = "#,##0.00"
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True

                        End With
                        With .Range("I" + countline.ToString + ":I" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNPRICE")
                            .NumberFormat = "#,##0.00"
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True

                        End With
                        With .Range("J" + countline.ToString + ":J" + countline.ToString)
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNPRICEDEPART")
                            .NumberFormat = "#,##0.00"
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True

                        End With
                        countline += 1

                        man_hour += CDbl(mySqlReader("CTNDEPOTHR"))

                        hrtotal += CInt(mySqlReader("CTNPRICE"))
                        departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                        sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))

                        If mySqlReader("CTNCODE") = "DM" Then
                            dmtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        ElseIf mySqlReader("CTNCODE") = "WT" Then
                            wttotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        ElseIf mySqlReader("CTNCODE") = "CN" Then
                            cntotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        End If

                    End While
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()




                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If

                mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
                ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

                mySqlCommand.Connection = mysql
                mySqlAdaptor.SelectCommand = mySqlCommand
                Try
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())

                        With .Range("A7:B7")
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("CTNSTRING")
                            containerlist = mySqlReader("CTNSTRING")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True
                        End With
                        Dim a As String
                        a = Mid(containerlist, 1, 1)
                        With .Range("C7:C7")
                            .Merge()
                            .Font.Size = 10
                            If a = "W" Then
                                .Value = "W496"
                            Else
                                .Value = "S496"
                            End If

                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True
                        End With


                        With .Range("F7:F7")
                            .Merge()
                            .Font.Size = 10
                            .Value = mySqlReader("VOYDATEEN")

                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True
                        End With


                        With .Range("G7:H7")
                            .Merge()
                            .Font.Size = 10
                            .WrapText = True
                            .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlCenter
                            .Font.Bold = True
                        End With



                    End While

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try



                With .Range("H31:H31")
                    .Merge()
                    .Font.Size = 10
                    .Value = man_hour
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With

                With .Range("I31:I31")
                    .Merge()
                    .Font.Size = 10
                    .Value = hrtotal
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With

                With .Range("J31:J31")
                    .Merge()
                    .Font.Size = 10
                    .Value = departotal
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With
                With .Range("D38:D38")
                    .Merge()
                    .Font.Size = 10
                    If dmtotal > 0 Then
                        .Value = dmtotal
                    Else

                    End If
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With
                With .Range("D40:D40")
                    .Merge()
                    .Font.Size = 10
                    If wttotal > 0 Then
                        .Value = wttotal
                    End If
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With
                With .Range("D42:D42")
                    .Merge()
                    .Font.Size = 10
                    If cntotal > 0 Then
                        .Value = cntotal
                    End If
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlRight
                    .Font.Bold = True
                End With

                With .Range("I7:J7")
                    .Merge()
                    .Font.Size = 10
                    .Value = sumtotal
                    .NumberFormat = "#,##0.00"
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With

            End With





            excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
            Try
                excelbooks.SaveAs(pathExcel.ToString + "\" + "DEPOTOther No." + containerlist + "  .xlsx")


                MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
                excelsheets = Nothing
                excelbooks.Close()


                excelapp.Quit()

                excelbooks = Nothing


                excelapp = Nothing
            Catch ex As Exception
                MsgBox(ex.ToString)
                MsgBox("NOT SAVED")
            End Try

    End Sub
    Private Sub excelreportRepair()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 6.13
            .Range("D1").ColumnWidth = 13.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 13.5
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            '.DisplayGridLines = False
            .Range("A1:L20").Font.Name = "Angsana New"
            .Range("A1:L20").Font.Size = 14
            .Range("A21:L400").Font.Name = "Times New Roman"
            .Range("A21:L400").Font.Size = 9
            .Rows("4:8").rowheight = 0
            .Rows("9:46").rowheight = 15
            .Rows("31:31").rowheight = 31

            For J = 7 To 10
                .Range("A29:B30").Borders(J).Weight = 3 ' xlThin
                .Range("C29:C30").Borders(J).Weight = 3 ' xlThin
                .Range("D29:D30").Borders(J).Weight = 3 ' xlThin
                .Range("E29:E30").Borders(J).Weight = 3 ' xlThin
                .Range("F29:F30").Borders(J).Weight = 3 ' xlThin
                .Range("G29:H30").Borders(J).Weight = 3 ' xlThin

                .Range("A31:B31").Borders(J).Weight = 3 ' xlThin
                .Range("C31:C31").Borders(J).Weight = 3 ' xlThin
                .Range("D31:D31").Borders(J).Weight = 3 ' xlThin
                .Range("E31:E31").Borders(J).Weight = 3 ' xlThin
                .Range("F31:F31").Borders(J).Weight = 3 ' xlThin
                .Range("G31:H31").Borders(J).Weight = 3 ' xlThin
                '-----
                .Range("A33:A34").Borders(J).Weight = 3 ' xlThin
                .Range("B33:E34").Borders(J).Weight = 3 ' xlThin
                .Range("F33:F34").Borders(J).Weight = 3 ' xlThin
                .Range("G33:H34").Borders(J).Weight = 3 ' xlThin

            Next

            .Range("A31:A48").Borders(7).Weight = 3 ' xlThin
            .Range("B34:B48").Borders(7).Weight = 3 ' xlThin

            .Range("F34:F48").Borders(7).Weight = 3 ' xlThin

            .Range("G34:G48").Borders(7).Weight = 3 ' xlThin
            .Range("I34:I48").Borders(7).Weight = 3 ' xlThin


            .Range("A48:H48").Borders(9).Weight = 2 ' xlThin

            With .Range("A1:H1")
                .Merge()
                .Value = "Estimate Of  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("A29:B29")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A30:B30")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C29:C29")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C30:C30")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D29:D29")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D30:D30")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E29:E29")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E30:E30")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F29:F29")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F30:F30")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G29:H29")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G30:H30")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A33:A33")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A34:A34")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B33:E33")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("B34:E34")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With



            With .Range("F33:F33")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("F34:F34")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With




            With .Range("G33:H33")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("G34:H34")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With








            With .Range("F50:H50")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :........................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            Dim countline As Integer = 35
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot left join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID  where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                With .Range("A" + countline.ToString + ":A" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = countitem.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countitem += 1

                With .Range("B" + countline.ToString + ":E" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCOMMENT")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True

                End With
                With .Range("F" + countline.ToString + ":F" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODEREPAIR")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("G" + countline.ToString + ":H" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODE")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countline += 1


            End While

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A31:B31")
                        .Merge()
                        .Font.Size = 10
                        containerlist = mySqlReader("CTNSTRING")
                        .Value = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    Dim a As String
                    a = Mid(containerlist, 1, 1)
                    With .Range("C31:C31")
                        .Merge()
                        .Font.Size = 6
                        If a = "W" Then
                            .Value = "W496"
                        Else
                            .Value = "S496"
                        End If

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                   
                    With .Range("D31:D31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("E31:E31")
                        .Merge()
                        .Font.Size = 10
                        .Value = "YUSOB PORT"

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("F31:F31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

               
                    With .Range("G31:H31")
                        .Merge()
                        .Font.Size = 10
                        .WrapText = True
                        .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("D31:D31")
                        .Merge()
                        .Font.Size = 10
                        .Value = " "
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End With

        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "DEPOTช่าง No." + containerlist + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
            CircularProgress1.IsRunning = False
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")
            CircularProgress1.IsRunning = False
        End Try




    End Sub
    Private Sub ButtonItem11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem11.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
     
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelall))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try
        End If

    End Sub
    Private Sub excelall()

        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        Dim excelsheets1 As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        'excelbooks.Windows(1).DisplayGridlines = False

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        With excelsheets

          Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_wanhai.png", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            '.DisplayGridLines = False
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10
            .Range("C1").ColumnWidth = 4.63
            .Range("D1").ColumnWidth = 8.52
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 15.13
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            .Range("I1").ColumnWidth = 11.13
            .Range("J1").ColumnWidth = 14.63
            For J = 7 To 10
                .Range("A6:B6").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C6").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D6").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E6").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F6").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H6").Borders(J).Weight = 3 ' xlThin
                .Range("I6:J6").Borders(J).Weight = 3 ' xlThin
                .Range("A7:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C7:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D7:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E7:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F7:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G7:H7").Borders(J).Weight = 3 ' xlThin
                .Range("I7:J7").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:G10").Borders(J).Weight = 3 ' xlThin
                .Range("H10:H10").Borders(J).Weight = 3 ' xlThin
                .Range("I10:I10").Borders(J).Weight = 3 ' xlThin
                .Range("J10:J10").Borders(J).Weight = 3 ' xlThin
                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next

            For J = 11 To 29
                .Range("A" + J.ToString + ":J" + J.ToString).Borders(9).Weight = 2 ' xlThin
            Next
            .Range("A11:A30").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B30").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F30").Borders(7).Weight = 3 ' xlThin
            .Range("G11:G30").Borders(7).Weight = 3 ' xlThin
            .Range("H11:H31").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(10).Weight = 3 ' xlThin
            .Range("H42:J42").Borders(9).Weight = 2 ' xlThin
            .Range("A44:J44").Borders(9).Weight = 2 ' xlThin
            .Range("A51:J51").Borders(9).Weight = 2 ' xlThin

            .Range("A1:L500").Font.Name = "Times New Roman"
            .Range("A1:L500").Font.Size = 8
            .Rows("1:1").rowheight = 30
            .Rows("2:2").rowheight = 20.25
            .Rows("3:3").rowheight = 20.25

            .Rows("4:4").rowheight = 20.25
            .Rows("5:31").rowheight = 16.25

            .Rows("8:8").rowheight = 15.75
            .Rows("10:10").rowheight = 17.25
            .Rows("33:54").rowheight = 15.75
            With .Range("A1:J1")
                .Merge()
                .Font.Size = 24
                .Value = "WAN HAI LINES LTD."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Font.Size = 10
                .Value = "YUSOB PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:J2")
                .Merge()
                .Font.Size = 16
                .Value = "EQUIPMENT REPAIR ESTIMATION REPORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I4:I4")
                .Font.Size = 12
                .Value = "EST NO.KAN 03"
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A6:B6")
                .Merge()
                .Font.Size = 10
                .Value = "CONTAINER NO"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Font.Size = 10
                .Value = ctnstring
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C6:C6")
                .Merge()
                .Font.Size = 10
                .Value = "SIZE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D6:D6")
                .Merge()
                .Font.Size = 10
                .Value = "EST.DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E6:E6")
                .Merge()
                .Font.Size = 10
                .Value = "LOADING PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F6:F6")
                .Merge()
                .Font.Size = 10
                .Value = "IMPORT DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G6:H6")
                .Merge()
                .Font.Size = 10
                .Value = "IMP VSL & VOY"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I6:J6")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A10:A10")

                .Font.Size = 10
                .Value = "ITEM"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Italic = True
                .Font.Bold = True

            End With
            With .Range("B10:E10")
                .Merge()
                .Font.Size = 10
                .Value = "DESCRIPTION OF DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F10:F10")

                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G10:G10")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("H10:H10")
                .Merge()
                .Font.Size = 10
                .Value = "MAN HOUR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("I10:I10")
                .Merge()
                .Font.Size = 10
                .Value = "LABOR COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J10:J10")
                .Merge()
                .Font.Size = 10
                .Value = "MATERIAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("A31:G31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A33:D33")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True

            End With

            With .Range("A38:C38")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A40:C40")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A42:C42")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("H42:I42")
                .Merge()
                .Font.Size = 10
                .Value = "APPROVED BY :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A44:C44")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A31:G31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Italic = True
            End With
            With .Range("A45:D45")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ----------------------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A46:D46")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN------------- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A47:D47")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP ------------------- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A48:D48")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ------------- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A49:D49")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH --------------------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E45:F45")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION ---------------------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E46:F46")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE --------------------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E47:F47")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT ------------------------ INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E48:F48")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE ----- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E49:F49")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------------------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("G45:I45")
                .Merge()
                .Font.Size = 10
                .Value = "FREE --------------------------- FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A51:B51")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A53:J53")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A54:J54")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A52:J52")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            Dim countline As Integer = 12
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0
            Dim dmtotal As Integer = 0
            Dim wttotal As Integer = 0
            Dim cntotal As Integer = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot left join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A" + countline.ToString + ":A" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = countitem.ToString
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countitem += 1

                    With .Range("B" + countline.ToString + ":E" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCOMMENTENG")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                        .Font.Bold = True

                    End With
                    With .Range("F" + countline.ToString + ":F" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODEREPAIR")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    With .Range("G" + countline.ToString + ":G" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("H" + countline.ToString + ":H" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNDEPOTHR")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("I" + countline.ToString + ":I" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICE")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("J" + countline.ToString + ":J" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICEDEPART")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countline += 1
                    Try
                        man_hour += CDbl(mySqlReader("CTNDEPOTHR"))

                        hrtotal += CInt(mySqlReader("CTNPRICE"))
                        departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                        sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))

                        If mySqlReader("CTNCODE") = "DM" Then
                            dmtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        ElseIf mySqlReader("CTNCODE") = "WT" Then
                            wttotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        ElseIf mySqlReader("CTNCODE") = "CN" Then
                            cntotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                        End If
                    Catch ex As Exception

                    End Try


                End While
            Catch ex As Exception

            End Try
            mysql.Close()




            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    With .Range("A7:B7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSTRING")
                        containerlist = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    Dim a As String
                    a = Mid(containerlist, 1, 1)
                    With .Range("C7:C7")
                        .Merge()
                        .Font.Size = 10
                        If a = "W" Then
                            .Value = "W496"
                        Else
                            .Value = "S496"
                        End If

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("F7:F7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("G7:H7")
                        .Merge()
                        .Font.Size = 10
                        .WrapText = True
                        .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With



                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try



            With .Range("H31:H31")
                .Merge()
                .Font.Size = 10
                .Value = man_hour
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("I31:I31")
                .Merge()
                .Font.Size = 10
                .Value = hrtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = departotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D38:D38")
                .Merge()
                .Font.Size = 10
                If dmtotal > 0 Then
                    .Value = dmtotal
                Else

                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D40:D40")
                .Merge()
                .Font.Size = 10
                If wttotal > 0 Then
                    .Value = wttotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With
            With .Range("D42:D42")
                .Merge()
                .Font.Size = 10
                If cntotal > 0 Then
                    .Value = cntotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlRight
                .Font.Bold = True
            End With

            With .Range("I7:J7")
                .Merge()
                .Font.Size = 10
                .Value = sumtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

        End With

        excelsheets1 = CType(excelbooks.Worksheets(2), Excel.Worksheet)
        'excelbooks.Windows(2).DisplayGridlines = False


        With excelsheets1

            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            Try
                .Shapes.AddPicture("\\ykpserver55\DB\ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception


            End Try
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 6.13
            .Range("D1").ColumnWidth = 13.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 13.5
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75

            .Range("A1:L20").Font.Name = "Angsana New"
            .Range("A1:L20").Font.Size = 14
            .Range("A21:L400").Font.Name = "Times New Roman"
            .Range("A21:L400").Font.Size = 9
            .Rows("4:8").rowheight = 0
            .Rows("9:46").rowheight = 15

            For J = 7 To 10
                .Range("A29:B30").Borders(J).Weight = 3 ' xlThin
                .Range("C29:C30").Borders(J).Weight = 3 ' xlThin
                .Range("D29:D30").Borders(J).Weight = 3 ' xlThin
                .Range("E29:E30").Borders(J).Weight = 3 ' xlThin
                .Range("F29:F30").Borders(J).Weight = 3 ' xlThin
                .Range("G29:H30").Borders(J).Weight = 3 ' xlThin

                .Range("A31:B31").Borders(J).Weight = 3 ' xlThin
                .Range("C31:C31").Borders(J).Weight = 3 ' xlThin
                .Range("D31:D31").Borders(J).Weight = 3 ' xlThin
                .Range("E31:E31").Borders(J).Weight = 3 ' xlThin
                .Range("F31:F31").Borders(J).Weight = 3 ' xlThin
                .Range("G31:H31").Borders(J).Weight = 3 ' xlThin
                '-----
                .Range("A33:A34").Borders(J).Weight = 3 ' xlThin
                .Range("B33:E34").Borders(J).Weight = 3 ' xlThin
                .Range("F33:F34").Borders(J).Weight = 3 ' xlThin
                .Range("G33:H34").Borders(J).Weight = 3 ' xlThin

            Next

            .Range("A31:A48").Borders(7).Weight = 3 ' xlThin
            .Range("B34:B48").Borders(7).Weight = 3 ' xlThin

            .Range("F34:F48").Borders(7).Weight = 3 ' xlThin

            .Range("G34:G48").Borders(7).Weight = 3 ' xlThin
            .Range("I34:I48").Borders(7).Weight = 3 ' xlThin


            .Range("A48:H48").Borders(9).Weight = 2 ' xlThin

            With .Range("A1:H1")
                .Merge()
                .Value = "Estimate Of  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("A29:B29")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A30:B30")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C29:C29")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C30:C30")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D29:D29")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D30:D30")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E29:E29")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E30:E30")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F29:F29")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F30:F30")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G29:H29")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G30:H30")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A33:A33")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A34:A34")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B33:E33")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("B34:E34")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With



            With .Range("F33:F33")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("F34:F34")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With




            With .Range("G33:H33")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With
            With .Range("G34:H34")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True

            End With








            With .Range("F50:H50")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :........................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            Dim countline As Integer = 35
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID  where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                With .Range("A" + countline.ToString + ":A" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = countitem.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countitem += 1

                With .Range("B" + countline.ToString + ":E" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCOMMENT")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True

                End With
                With .Range("F" + countline.ToString + ":F" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODEREPAIR")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("G" + countline.ToString + ":H" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODE")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countline += 1


            End While

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A31:B31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSTRING")
                        ctnname = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("C31:C31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSIZE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("E31:E31")
                        .Merge()
                        .Font.Size = 10
                        .Value = "YUSOB PORT"

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("F31:F31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("G31:H31")
                        .Merge()
                        .Font.Size = 10
                        .Value = "N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    Dim a As String
                    a = Mid(containerlist, 1, 1)
                    With .Range("C31:C31")
                        .Merge()
                        .Font.Size = 6
                        If a = "W" Then
                            .Value = "W496"
                        Else
                            .Value = "S496"
                        End If

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try






        End With
        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False



        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "" + ctnname + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelsheets1 = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
            CircularProgress1.IsRunning = False
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")
            CircularProgress1.IsRunning = False
        End Try

    End Sub

    Private Sub ButtonItem9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem9.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
    
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelMalay))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try
        End If
    End Sub

    Private Sub ButtonItem10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem10.Click

        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelOther))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try
        End If

    End Sub

    

    Private Sub DataGridViewX1_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX1.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub DataGridViewX2_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX2.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub DataGridViewX3_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX3.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub DataGridViewX4_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX4.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub ButtonItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem6.Click
        Dim cf As New frmviewdepot_stat
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub

   
    Private Sub ButtonItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem2.Click

    End Sub
End Class